import xlrd
import csv
import codecs
import argparse
import os
import time

def get_dir_all_file(path,suffix):
    # 获取指定目录下的所有指定后缀的文件名 
    input_template_All=[]
    f_list = os.listdir(path)#返回文件名
    for i in f_list:
        # os.path.splitext():分离文件名与扩展名
        if os.path.splitext(i)[1] ==suffix:
            input_template_All.append(path + i)
            #print(i)
    return input_template_All

def args_parse():
    # construct the argument parse and parse the arguments
    ap = argparse.ArgumentParser()
    ap.add_argument("-f", "--file", 
                    required=True,
                    help="excel file name")
    args = vars(ap.parse_args()) 
    return args
 
def xlsx_to_csv_pd(filepath,outpath):
    print(filepath)
    print(outpath)
    workbook = xlrd.open_workbook(filepath)
    sheets = workbook.sheet_names()
    for i in range(len(sheets)):
        table = workbook.sheet_by_index(i)
        sht = outpath + sheets[i] + '.csv'
        with codecs.open(sht, 'w', encoding='UTF-8') as f:
            write = csv.writer(f)
            col_num = table.ncols
            row_num = table.nrows
            read_type_hash = {}
            #all_data = {}
            for row in range(row_num):
                if row < 2:
                    continue
                row_data = []
                for col in range(col_num):
                    cell_val = table.cell_value(row,col)
                   # print(cell_val)
                    if row == 2:
                        read_type_hash[col] = cell_val
                        continue
                    if read_type_hash[col] != 0:
                        row_data.append(cell_val)
                #all_data[row]=row_data
                if row > 2:
                    write.writerow(row_data)
                #row_value = table.row_values(row)
                #write.writerow(row_value)
        print(sht," created!")
        
if __name__=='__main__':
    st = time.time()
    #args = args_parse()
    #filepath = args['file']
    cur_dir = os.getcwd() 
    out_file_list = get_dir_all_file( cur_dir + "\\xlsx\\",".xlsx")
    for file_name in out_file_list:
        xlsx_to_csv_pd(file_name,os.getcwd() + "\\csv\\")
        print("Completed to convert ",file_name," to csv files!")
    nd = time.time()
    tm = nd - st
    print("Spend time: ",tm,"s")
